package cn.chainco.cts.utils.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.struts2.ServletActionContext;

public class ExportExcel{

	private static final long serialVersionUID = 1L;
	private String text;

	public String getText(){
		return text;
	}

	public void setText(String text){
		this.text = text;
	}

	public void prepare() throws Exception{
	}

	public void init(String Title, String FileName, List<List<ExcelTitle>> TitleList, List<List<ExcelTitle>> HeadList,
			List<List<ExcelProperty>> dataList, int col) throws Exception{

		HttpServletResponse response = ServletActionContext.getResponse();
		HSSFWorkbook workbook = exportExcel(Title, TitleList, HeadList, dataList, col);
		if(workbook != null){
			this.printExcel(workbook, response, FileName + ".xls");
		}
	}

	private void printExcel(HSSFWorkbook workbook, HttpServletResponse response, String string) throws IOException{
		OutputStream out = response.getOutputStream();
		HttpServletRequest request = ServletActionContext.getRequest();
		if("FF".equals(getBrowser(request))){
			//针对火狐浏览器处理
			string = new String(string.getBytes("UTF-8"), "iso-8859-1");
		}else{
			string = URLEncoder.encode(string, "UTF-8");
		}
		response.setCharacterEncoding("UTF-8");
		response.setHeader("Content-disposition", "attachment;filename=" + string);
		response.setContentType("application/x-msdownload;charset=UTF-8");
		workbook.write(out);
		out.flush();
		out.close();

	}
	
	private String getBrowser(HttpServletRequest request){
	    String UserAgent = request.getHeader("USER-AGENT").toLowerCase();
	    if(UserAgent!=null){
	        if (UserAgent.indexOf("msie") >=0 ) return "IE";
	        if (UserAgent.indexOf("firefox") >= 0) return "FF";
	        if (UserAgent.indexOf("safari") >= 0) return "SF";
	    }
	    return null;
	}

	public HSSFWorkbook exportExcel(String Title, List<List<ExcelTitle>> TitleList, List<List<ExcelTitle>> HeadList,
			List<List<ExcelProperty>> dataList, int col) throws Exception{
		HSSFWorkbook workbook = null;
		try{
			workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("TscExcel");
			this.setSheetColumnWidth(sheet, col);
			HSSFCellStyle style = this.createTitleStyle(workbook);
			HSSFCellStyle head = this.createHeadStyle(workbook);
			HSSFCellStyle cell = this.createCellStyle(workbook);
			if(dataList != null && dataList.size() > 0){
				int Rows = 0;
				int index = -1;
				if(Title != null){
					HSSFRow Firstrow = sheet.createRow((short) Rows++);
					this.createCell(Firstrow, 0, head, HSSFCell.CELL_TYPE_STRING, Title);
					sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) (col - 1)));
					Rows++;
				}
				for(int i = 0; i < TitleList.size(); i++){
					HSSFRow row = sheet.createRow((short) Rows++);
					List<ExcelTitle> list = TitleList.get(i);
					for(int j = 0; j < list.size(); j++){
						ExcelTitle mode = list.get(j);

						this.createCell(row, mode.getCol(), style, HSSFCell.CELL_TYPE_STRING, mode.getName()
								+ (mode.getValue() == null ? "" : mode.getValue()));
						if(index != -1 && mode.getCol() - index > 1)
							sheet.addMergedRegion(new Region(Rows - 1, (short) index, Rows - 1,
									(short) (mode.getCol() - 1)));  
						if(mode.getRow() > 1)
							sheet.addMergedRegion(new Region(Rows - 1, (short) mode.getCol(), Rows, (short) mode
									.getCol()));  
						index = mode.getCol();
					}
					if(index < col)
						sheet.addMergedRegion(new Region(Rows - 1, (short) index, Rows - 1, (short) (col - 1)));
					index = -1;
				}
				for(int i = 0; i < HeadList.size(); i++){
					HSSFRow row = sheet.createRow((short) Rows++);
					List<ExcelTitle> list = HeadList.get(i);
					for(int j = 0; j < list.size(); j++){
						ExcelTitle mode = list.get(j);

						this.createCell(row, mode.getCol(), style, HSSFCell.CELL_TYPE_STRING,
								mode.getName() == null ? "" : mode.getName());
						if(index != -1 && mode.getCol() - index > 1)
							sheet.addMergedRegion(new Region(Rows - 1, (short) index, Rows - 1,
									(short) (mode.getCol() - 1)));
						if(mode.getRow() > 1)
							sheet.addMergedRegion(new Region(Rows - 1, (short) mode.getCol(), Rows, (short) mode
									.getCol())); 
						index = mode.getCol();
					}
					if(index < col)
						sheet.addMergedRegion(new Region(Rows - 1, (short) index, Rows - 1, (short) (col - 1)));

				}
				for(int i = 0; i < dataList.size(); i++){
					List<ExcelProperty> list = (List<ExcelProperty>) dataList.get(i);

					HSSFRow row1 = sheet.createRow((short) (Rows++));  
					for(int j = 0; j < col; j++){
						ExcelProperty model = (ExcelProperty) list.get(j);
						if(model.getValue() != null){
							this.createCell(row1, model.getCol(), cell, model.getType() == ExcelProperty.CONTENT_STRING ? HSSFCell.CELL_TYPE_STRING : HSSFCell.CELL_TYPE_NUMERIC, model.getValue());
						}
					}
				}
			}else{
				this.createCell(sheet.createRow(0), 0, style, HSSFCell.CELL_TYPE_STRING, "查无资料");
			}
		}catch (Exception e){
			e.printStackTrace();
		}
		return workbook;
	}

	private void setSheetColumnWidth(HSSFSheet sheet, int col){
		for(int i = 0; i < col; i++)
			sheet.setColumnWidth((short) i, (short) 3000);
	}

	private HSSFCellStyle createTitleStyle(HSSFWorkbook wb){
		HSSFFont boldFont = wb.createFont();
		boldFont.setFontHeight((short) 200);
		HSSFCellStyle style = wb.createCellStyle();
		style.setFont(boldFont);
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		return style;
	}

	private HSSFCellStyle createHeadStyle(HSSFWorkbook wb){
		HSSFFont boldFont = wb.createFont();
		boldFont.setFontHeight((short) 200);
		HSSFCellStyle style = wb.createCellStyle();
		style.setFont(boldFont);
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		boldFont.setFontHeightInPoints((short) 18);
		boldFont.setFontName("黑体");
		boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(boldFont);

		return style;
	}

	private HSSFCellStyle createCellStyle(HSSFWorkbook wb){

		HSSFCellStyle style = wb.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		return style;
	}

	@SuppressWarnings("deprecation")
	private void createCell(HSSFRow row, int column, HSSFCellStyle style, int cellType, Object value){
		HSSFCell cell = row.createCell((short) column);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		if(style != null){
			cell.setCellStyle(style);
		}
		switch (cellType) {
		case HSSFCell.CELL_TYPE_BLANK: {
		}
			break;
		case HSSFCell.CELL_TYPE_STRING: {
			cell.setCellValue(value.toString());
		}
			break;
		case HSSFCell.CELL_TYPE_NUMERIC: {
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue(Double.parseDouble(value.toString()));
		}
			break;
		default:
			break;
		}
	}
}
